package com.ustcinfo.study.hive.r2.mazheng;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Describe:  hive connection，show count of items
 * code by mazheng at 2018/8/3 20:50
 */


public class TestHive {

    // hive链接地址,主机mz0(192.168.200.100),端口10000，数据库default
    private static String url = "jdbc:hive2://mz0:10000/default";

    //hive用户名：使用配置文件里设置的hive
    private static String userName = "hive";

    // hive密码，与用户名相同，都是hive
    private static String passwd = "hive";

    // hive 驱动 格式固定的
    private static String driver = "org.apache.hive.jdbc.HiveDriver";

    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    // 查询tables sql
    //private static String showTableSql = "show tables";
    //查看相同域名的人的访问次数
    private static String countSql= "select domain_name,sount(domain_name)as DoMainNameCount from countip group by domain_name";
    //查询访问前十的域名
    private static String coutSql1 =  "select domain_name,sount(domain_name)as DoMainNameCount from countip group by domain_name DESC LIMIT 10";
    
    //按时间进行域名统计

    public static void main(String [] args) throws SQLException {

        //  加载hive驱动,如果失败抛出异常
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }

        try {
            // 创建连接，连接hive，如果出错，抛出异常
            connection = DriverManager.getConnection(url, userName, passwd);
            if (connection != null) {
                System.out.println("hive 连接成功");
            } else {
                System.out.println("hive 连接失败");
            }
            
            //
            statement = connection.createStatement();

            // 获取数据库中所有的表
            //resultSet = statement.executeQuery(showTableSql);
           /* while (resultSet.next()) {
                System.out.println(resultSet.getString(1));
            }*/
            
            //获取域名和域名的数量的集合
            resultSet = statement.executeQuery(countSql);
            //显示一下
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1) + "\t" + resultSet.getInt(2));
            }

        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        } finally {
        	//不管打开什么都关掉，后打开的先关掉
            if(connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        }


    }

}
